-- 单证域主题宽表 ，将订单运单完结件表等关键字段聚合到一起，方便报表开发，该表保证最近16天数据唯一且完整，如使用超过15天的数据，需进行去重方可使用(partition by waybill_no order by dt desc)
with network as (   -- 网点维度表，用于取网点维度信息，加盟商，代理区
   select
          code
        ,name
        ,franchisee_code
        ,franchisee_name
        ,agent_code
        ,agent_name
   from jms_dim.dim_sys_network_detail_dt
   where dt='{{ execution_date | cst_ds }}'
),
way as (             -- 运单表，主表，取16天内新增与更新的所有单据
    select 
           waybill_no                    
         ,waybill_status_code          
         ,goods_type_id                
         ,goods_type_code              
         ,goods_type_name              
         ,goods_name                   
         ,package_length               
         ,package_wide                 
         ,package_high                 
         ,package_volume               
         ,package_total_weight         
         ,package_total_volume         
         ,package_charge_weight        
         ,box_standard_code            
         ,box_standard_name            
         ,collect_staff_code           
         ,collect_staff_name           
         ,collect_time                 
         ,dispatch_staff_code          
         ,dispatch_staff_name          
         ,dispatch_time                
         ,dispatch_network_code        
         ,dispatch_network_name        
         ,exception_code               
         ,exception_reason             
         ,terminal_dispatch_code       
         ,nullif(split(terminal_dispatch_code, ',')[0], '') as first_code                   
         ,nullif(split(terminal_dispatch_code, ',')[1], '') as second_code                  
         ,nullif(split(terminal_dispatch_code, ',')[2], '') as third_code                   
         ,is_sign                      
         ,sign_network_code            
         ,sign_network_name            
         ,sign_finance_code            
         ,sign_time                    
         ,is_abnormal                  
         ,abnormal_reg_network_code    
         ,abnormal_reg_time            
         ,waybill_source_code          
         ,waybill_source_name          
         ,send_code                    
         ,send_name                    
         ,dispatch_code                
         ,dispatch_name                
         ,is_void                      
         ,is_refund                    
         ,is_need_receipt              
         ,receipt_no                   
         ,customer_code                
         ,customer_name                
         ,sender_name                  
         ,sender_mobile_phone          
         ,sender_province_id           
         ,sender_province_name         
         ,sender_city_id               
         ,sender_city_name             
         ,sender_area_id               
         ,sender_area_name             
         ,sender_township              
         ,sender_street                
         ,sender_detailed_address      
         ,origin_id                    
         ,origin_name                  
         ,pick_network_code            
         ,pick_network_name            
         ,freight                      
         ,total_freight                
         ,pick_finance_code            
         ,dispatch_finance_code        
         ,receiver_name                
         ,receiver_mobile_phone        
         ,receiver_province_id         
         ,receiver_province_name       
         ,receiver_city_id             
         ,receiver_city_name           
         ,receiver_area_id             
         ,receiver_area_name           
         ,receiver_township            
         ,receiver_street              
         ,receiver_detailed_address    
         ,receiver_postal_code         
         ,destination_id               
         ,destination_name             
         ,receiver_sorting_code        
         ,input_time as waybill_input_time           
         ,input_network_code           
         ,settlement_weight            
         ,customer_id                  
         ,last_update_time             
         ,is_delete                    
         ,is_plaintext                 
         ,real_name                    
         ,id_no_type                   
         ,sex                          
         ,distribute_finance_code      
         ,package_collect_weight       
         ,package_receipt_weight       
         ,package_arrival_weight       
         ,package_inside_charge_weight 
         ,is_distribute_scan           
         ,is_arrival_scan              
         ,waybill_weight               
         ,is_business                  
         ,is_receive                   
         ,customer_network_code        
         ,null as order_id
         ,null as customer_order_id
         ,null as order_source_code
         ,null as order_source_name
         ,null as order_type_code
         ,null as packate_volume
         ,null as customer_order_time
         ,null as order_input_time
         ,null as order_status_code
         ,null as cancel_time
         ,null as real_pick_network_code
         ,null as dispatch_network_time
         ,null as pick_time
         ,null as assigner_name
         ,null as assigner_mobile_phone
         ,null as order_update_time
         ,null as create_by_code
         ,null as create_by_name
         ,null as proxy_area_code
         ,null as proxy_area_name
         ,null as cancel_explain
         ,null as customer_waybill_no
         ,null as sign_receipt
         ,null as receipt_waybill_no
         ,null as is_transfer
         ,null as order_pick_network_code
         ,null as order_dispatch_network_code
         ,null as end_code
         ,null as end_code_desc
         ,null as end_time
         ,null as end_network_code
         ,null as end_source
         ,null as order_source
         ,dt
      from
         ( select s.*,row_number() over(partition by waybill_no order by dt desc)  as rank
              from jms_ods.yl_oms_oms_waybill s
                where dt BETWEEN date_add('{{ execution_date | cst_ds }}', -15) AND '{{ execution_date | cst_ds }}'
          ) tmp  where rank = 1
),
ord_tmp as (     -- 订单表，取25(后续需要改为40)天数据(为了保证绝大部分运单表中数据都能关联出订单信息)，取运单表中存在单据以及15天内新增与更新的数据
   select
          waybill_id as waybill_no
         ,id as order_id
         ,customer_order_id
         ,order_source_code
         ,order_source_name
         ,order_type_code
         ,packate_volume
         ,customer_order_time
         ,input_time as order_input_time
         ,order_status_code
         ,cancel_time
         ,real_pick_network_code
         ,dispatch_network_time
         ,pick_time
         ,assigner_name
         ,assigner_mobile_phone
         ,update_time as order_update_time
         ,create_by_code
         ,create_by_name
         ,proxy_area_code
         ,proxy_area_name
         ,cancel_explain
         ,customer_waybill_no
         ,sign_receipt
         ,receipt_waybill_no
         ,is_transfer
         ,pick_network_code        as order_pick_network_code
         ,dispatch_network_code    as order_dispatch_network_code
         ,dt
   from
         ( select s.*,row_number() over(partition by id order by dt desc)  as rank
                from jms_ods.yl_oms_oms_order s
                where dt BETWEEN date_add('{{ execution_date | cst_ds }}', -25) AND '{{ execution_date | cst_ds }}'
          ) tmp  where rank = 1
),
ord as (
   select
          waybill_no
         ,null as waybill_status_code
         ,null as goods_type_id
         ,null as goods_type_code
         ,null as goods_type_name
         ,null as goods_name
         ,null as package_length
         ,null as package_wide
         ,null as package_high
         ,null as package_volume
         ,null as package_total_weight
         ,null as package_total_volume
         ,null as package_charge_weight
         ,null as box_standard_code
         ,null as box_standard_name
         ,null as collect_staff_code
         ,null as collect_staff_name
         ,null as collect_time
         ,null as dispatch_staff_code
         ,null as dispatch_staff_name
         ,null as dispatch_time
         ,null as dispatch_network_code
         ,null as dispatch_network_name
         ,null as exception_code
         ,null as exception_reason
         ,null as terminal_dispatch_code
         ,null as first_code
         ,null as second_code
         ,null as third_code
         ,null as is_sign
         ,null as sign_network_code
         ,null as sign_network_name
         ,null as sign_finance_code
         ,null as sign_time
         ,null as is_abnormal
         ,null as abnormal_reg_network_code
         ,null as abnormal_reg_time
         ,null as waybill_source_code
         ,null as waybill_source_name
         ,null as send_code
         ,null as send_name
         ,null as dispatch_code
         ,null as dispatch_name
         ,null as is_void
         ,null as is_refund
         ,null as is_need_receipt
         ,null as receipt_no
         ,null as customer_code
         ,null as customer_name
         ,null as sender_name
         ,null as sender_mobile_phone
         ,null as sender_province_id
         ,null as sender_province_name
         ,null as sender_city_id
         ,null as sender_city_name
         ,null as sender_area_id
         ,null as sender_area_name
         ,null as sender_township
         ,null as sender_street
         ,null as sender_detailed_address
         ,null as origin_id
         ,null as origin_name
         ,null as pick_network_code
         ,null as pick_network_name
         ,null as freight
         ,null as total_freight
         ,null as pick_finance_code
         ,null as dispatch_finance_code
         ,null as receiver_name
         ,null as receiver_mobile_phone
         ,null as receiver_province_id
         ,null as receiver_province_name
         ,null as receiver_city_id
         ,null as receiver_city_name
         ,null as receiver_area_id
         ,null as receiver_area_name
         ,null as receiver_township
         ,null as receiver_street
         ,null as receiver_detailed_address
         ,null as receiver_postal_code
         ,null as destination_id
         ,null as destination_name
         ,null as receiver_sorting_code
         ,null as waybill_input_time
         ,null as input_network_code
         ,null as settlement_weight
         ,null as customer_id
         ,null as last_update_time
         ,null as is_delete
         ,null as is_plaintext
         ,null as real_name
         ,null as id_no_type
         ,null as sex
         ,null as distribute_finance_code
         ,null as package_collect_weight
         ,null as package_receipt_weight
         ,null as package_arrival_weight
         ,null as package_inside_charge_weight
         ,null as is_distribute_scan
         ,null as is_arrival_scan
         ,null as waybill_weight
         ,null as is_business
         ,null as is_receive
         ,null as customer_network_code
         ,order_id
         ,customer_order_id
         ,order_source_code
         ,order_source_name
         ,order_type_code
         ,packate_volume
         ,customer_order_time
         ,order_input_time
         ,order_status_code
         ,cancel_time
         ,real_pick_network_code
         ,dispatch_network_time
         ,pick_time
         ,assigner_name
         ,assigner_mobile_phone
         ,order_update_time
         ,create_by_code
         ,create_by_name
         ,proxy_area_code
         ,proxy_area_name
         ,cancel_explain
         ,customer_waybill_no
         ,sign_receipt
         ,receipt_waybill_no
         ,is_transfer
         ,order_pick_network_code
         ,order_dispatch_network_code
         ,null as end_code
         ,null as end_code_desc
         ,null as end_time
         ,null as end_network_code
         ,null as end_source
         ,null as order_source
         ,dt
      from
         ord_tmp
      where waybill_no is not null
),
endd as (       -- 完结件表 ，获取完结信息
   select
          waybill_no
         ,null as waybill_status_code
         ,null as goods_type_id
         ,null as goods_type_code
         ,null as goods_type_name
         ,null as goods_name
         ,null as package_length
         ,null as package_wide
         ,null as package_high
         ,null as package_volume
         ,null as package_total_weight
         ,null as package_total_volume
         ,null as package_charge_weight
         ,null as box_standard_code
         ,null as box_standard_name
         ,null as collect_staff_code
         ,null as collect_staff_name
         ,null as collect_time
         ,null as dispatch_staff_code
         ,null as dispatch_staff_name
         ,null as dispatch_time
         ,null as dispatch_network_code
         ,null as dispatch_network_name
         ,null as exception_code
         ,null as exception_reason
         ,null as terminal_dispatch_code
         ,null as first_code
         ,null as second_code
         ,null as third_code
         ,null as is_sign
         ,null as sign_network_code
         ,null as sign_network_name
         ,null as sign_finance_code
         ,null as sign_time
         ,null as is_abnormal
         ,null as abnormal_reg_network_code
         ,null as abnormal_reg_time
         ,null as waybill_source_code
         ,null as waybill_source_name
         ,null as send_code
         ,null as send_name
         ,null as dispatch_code
         ,null as dispatch_name
         ,null as is_void
         ,null as is_refund
         ,null as is_need_receipt
         ,null as receipt_no
         ,null as customer_code
         ,null as customer_name
         ,null as sender_name
         ,null as sender_mobile_phone
         ,null as sender_province_id
         ,null as sender_province_name
         ,null as sender_city_id
         ,null as sender_city_name
         ,null as sender_area_id
         ,null as sender_area_name
         ,null as sender_township
         ,null as sender_street
         ,null as sender_detailed_address
         ,null as origin_id
         ,null as origin_name
         ,null as pick_network_code
         ,null as pick_network_name
         ,null as freight
         ,null as total_freight
         ,null as pick_finance_code
         ,null as dispatch_finance_code
         ,null as receiver_name
         ,null as receiver_mobile_phone
         ,null as receiver_province_id
         ,null as receiver_province_name
         ,null as receiver_city_id
         ,null as receiver_city_name
         ,null as receiver_area_id
         ,null as receiver_area_name
         ,null as receiver_township
         ,null as receiver_street
         ,null as receiver_detailed_address
         ,null as receiver_postal_code
         ,null as destination_id
         ,null as destination_name
         ,null as receiver_sorting_code
         ,null as waybill_input_time
         ,null as input_network_code
         ,null as settlement_weight
         ,null as customer_id
         ,null as last_update_time
         ,null as is_delete
         ,null as is_plaintext
         ,null as real_name
         ,null as id_no_type
         ,null as sex
         ,null as distribute_finance_code
         ,null as package_collect_weight
         ,null as package_receipt_weight
         ,null as package_arrival_weight
         ,null as package_inside_charge_weight
         ,null as is_distribute_scan
         ,null as is_arrival_scan
         ,null as waybill_weight
         ,null as is_business
         ,null as is_receive
         ,null as customer_network_code
         ,null as order_id
         ,null as customer_order_id
         ,null as order_source_code
         ,null as order_source_name
         ,null as order_type_code
         ,null as packate_volume
         ,null as customer_order_time
         ,null as order_input_time
         ,null as order_status_code
         ,null as cancel_time
         ,null as real_pick_network_code
         ,null as dispatch_network_time
         ,null as pick_time
         ,null as assigner_name
         ,null as assigner_mobile_phone
         ,null as order_update_time
         ,null as create_by_code
         ,null as create_by_name
         ,null as proxy_area_code
         ,null as proxy_area_name
         ,null as cancel_explain
         ,null as customer_waybill_no
         ,null as sign_receipt
         ,null as receipt_waybill_no
         ,null as is_transfer
         ,null as order_pick_network_code
         ,null as order_dispatch_network_code
         ,end_code
         ,end_code_desc
         ,end_time
         ,network_code as end_network_code
         ,source       as end_source
         ,null as order_source
         ,'1970-01-01' as dt
            from
         ( select s.*,row_number() over(partition by waybill_no order by end_time desc)  as rank
                from jms_ods.tab_end_piece s
              where dt = '{{ execution_date | cst_ds }}'
          ) tmp  where rank = 1
),
statistics as (  -- 访问edi接口记录明细表 ，获取订单来源(补充)
   select
          way_bill_no as waybill_no
         ,null as waybill_status_code
         ,null as goods_type_id
         ,null as goods_type_code
         ,null as goods_type_name
         ,null as goods_name
         ,null as package_length
         ,null as package_wide
         ,null as package_high
         ,null as package_volume
         ,null as package_total_weight
         ,null as package_total_volume
         ,null as package_charge_weight
         ,null as box_standard_code
         ,null as box_standard_name
         ,null as collect_staff_code
         ,null as collect_staff_name
         ,null as collect_time
         ,null as dispatch_staff_code
         ,null as dispatch_staff_name
         ,null as dispatch_time
         ,null as dispatch_network_code
         ,null as dispatch_network_name
         ,null as exception_code
         ,null as exception_reason
         ,null as terminal_dispatch_code
         ,null as first_code
         ,null as second_code
         ,null as third_code
         ,null as is_sign
         ,null as sign_network_code
         ,null as sign_network_name
         ,null as sign_finance_code
         ,null as sign_time
         ,null as is_abnormal
         ,null as abnormal_reg_network_code
         ,null as abnormal_reg_time
         ,null as waybill_source_code
         ,null as waybill_source_name
         ,null as send_code
         ,null as send_name
         ,null as dispatch_code
         ,null as dispatch_name
         ,null as is_void
         ,null as is_refund
         ,null as is_need_receipt
         ,null as receipt_no
         ,null as customer_code
         ,null as customer_name
         ,null as sender_name
         ,null as sender_mobile_phone
         ,null as sender_province_id
         ,null as sender_province_name
         ,null as sender_city_id
         ,null as sender_city_name
         ,null as sender_area_id
         ,null as sender_area_name
         ,null as sender_township
         ,null as sender_street
         ,null as sender_detailed_address
         ,null as origin_id
         ,null as origin_name
         ,null as pick_network_code
         ,null as pick_network_name
         ,null as freight
         ,null as total_freight
         ,null as pick_finance_code
         ,null as dispatch_finance_code
         ,null as receiver_name
         ,null as receiver_mobile_phone
         ,null as receiver_province_id
         ,null as receiver_province_name
         ,null as receiver_city_id
         ,null as receiver_city_name
         ,null as receiver_area_id
         ,null as receiver_area_name
         ,null as receiver_township
         ,null as receiver_street
         ,null as receiver_detailed_address
         ,null as receiver_postal_code
         ,null as destination_id
         ,null as destination_name
         ,null as receiver_sorting_code
         ,null as waybill_input_time
         ,null as input_network_code
         ,null as settlement_weight
         ,null as customer_id
         ,null as last_update_time
         ,null as is_delete
         ,null as is_plaintext
         ,null as real_name
         ,null as id_no_type
         ,null as sex
         ,null as distribute_finance_code
         ,null as package_collect_weight
         ,null as package_receipt_weight
         ,null as package_arrival_weight
         ,null as package_inside_charge_weight
         ,null as is_distribute_scan
         ,null as is_arrival_scan
         ,null as waybill_weight
         ,null as is_business
         ,null as is_receive
         ,null as customer_network_code
         ,null as order_id
         ,null as customer_order_id
         ,null as order_source_code
         ,null as order_source_name
         ,null as order_type_code
         ,null as packate_volume
         ,null as customer_order_time
         ,null as order_input_time
         ,null as order_status_code
         ,null as cancel_time
         ,null as real_pick_network_code
         ,null as dispatch_network_time
         ,null as pick_time
         ,null as assigner_name
         ,null as assigner_mobile_phone
         ,null as order_update_time
         ,null as create_by_code
         ,null as create_by_name
         ,null as proxy_area_code
         ,null as proxy_area_name
         ,null as cancel_explain
         ,null as customer_waybill_no
         ,null as sign_receipt
         ,null as receipt_waybill_no
         ,null as is_transfer
         ,null as order_pick_network_code
         ,null as order_dispatch_network_code
         ,null as end_code
         ,null as end_code_desc
         ,null as end_time
         ,null as end_network_code
         ,null as end_source
         ,order_source
         ,'1970-01-01' as dt
            from
         ( select s.*,row_number() over(partition by way_bill_no order by id desc)  as rank
                from jms_ods.tab_statistics s
                where dt BETWEEN date_add('{{ execution_date | cst_ds }}', -25) AND '{{ execution_date | cst_ds }}'
          ) tmp  where rank = 1
),
aggr as (
select * from way
union all
select * from ord
union all
select * from endd
union all
select * from statistics
),
result as (
  select
          waybill_no                             as   waybill_no                        -- 运单号
          ,max(waybill_status_code)              as   waybill_status_code               -- 运单状态code
          ,max(goods_type_id)                    as   goods_type_id                     -- 物品类型id
          ,max(goods_type_code)                  as   goods_type_code                   -- 物品类型code
          ,max(goods_type_name)                  as   goods_type_name                   -- 物品类型名称
          ,max(goods_name)                       as   goods_name                        -- 物品名称
          ,max(package_length)                   as   package_length                    -- 包裹总长单位厘米
          ,max(package_wide)                     as   package_wide                      -- 包裹总宽单位厘米
          ,max(package_high)                     as   package_high                      -- 包裹总高单位厘米
          ,max(package_volume)                   as   package_volume                    -- 包裹体积重单位千克
          ,max(package_total_weight)             as   package_total_weight              -- 包裹总重量单位: 千克
          ,max(package_total_volume)             as   package_total_volume              -- 包裹总体积单位立方厘米
          ,max(package_charge_weight)            as   package_charge_weight             -- 包裹计费重量单位千克
          ,max(box_standard_code)                as   box_standard_code                 -- 箱子规格code
          ,max(box_standard_name)                as   box_standard_name                 -- 箱子规格名称
          ,max(collect_staff_code)               as   collect_staff_code                -- 揽件业务员code
          ,max(collect_staff_name)               as   collect_staff_name                -- 揽件业务员名称
          ,max(collect_time)                     as   collect_time                      -- 揽件时间
          ,max(dispatch_staff_code)              as   dispatch_staff_code               -- 派件业务员code
          ,max(dispatch_staff_name)              as   dispatch_staff_name               -- 派件业务员名称
          ,max(dispatch_time)                    as   dispatch_time                     -- 派件时间
          ,max(dispatch_network_code)            as   dispatch_network_code             -- 派件网点code
          ,max(dispatch_network_name)            as   dispatch_network_name             -- 派件网点名称
          ,max(exception_code)                   as   exception_code                    -- 异常原因编码
          ,max(exception_reason)                 as   exception_reason                  --
          ,max(terminal_dispatch_code)           as   terminal_dispatch_code            -- 三段码
          ,max(first_code)                       as   first_code                        -- 一段码
          ,max(second_code)                      as   second_code                       -- 二段码
          ,max(third_code)                       as   third_code                        -- 三段码
          ,max(is_sign)                          as   is_sign                           -- 签收标识1是，0否
          ,max(sign_network_code)                as   sign_network_code                 -- 签收网点code
          ,max(sign_network_name)                as   sign_network_name                 -- 签收网点名称
          ,max(sign_finance_code)                as   sign_finance_code                 -- 签收财务网点编码
          ,max(sign_time)                        as   sign_time                         -- 签收时间
          ,max(is_abnormal)                      as   is_abnormal                       -- 问题件标识1是，0否
          ,max(abnormal_reg_network_code)        as   abnormal_reg_network_code         -- 问题件登记网点code
          ,max(abnormal_reg_time)                as   abnormal_reg_time                 -- 问题件登记时间
          ,max(waybill_source_code)              as   waybill_source_code               -- 运单来源code
          ,max(waybill_source_name)              as   waybill_source_name               -- 运单来源名称
          ,max(send_code)                        as   send_code                         -- 寄件方式code
          ,max(send_name)                        as   send_name                         -- 寄件方式名称
          ,max(dispatch_code)                    as   dispatch_code                     -- 派件方式code
          ,max(dispatch_name)                    as   dispatch_name                     -- 派件方式名称
          ,max(is_void)                          as   is_void                           -- 是否作废件1是,0否
          ,max(is_refund)                        as   is_refund                         -- 是否退件1是,0否
          ,max(is_need_receipt)                  as   is_need_receipt                   -- 是否需要需要签回单1是,0否
          ,max(receipt_no)                       as   receipt_no                        -- 回单编号
          ,max(customer_code)                    as   customer_code                     -- 客户编号code
          ,max(customer_name)                    as   customer_name                     -- 客户编号名称
          ,max(sender_name)                      as   sender_name                       -- 寄件人姓名
          ,max(sender_mobile_phone)              as   sender_mobile_phone               -- 寄件人手机号
          ,max(sender_province_id)               as   sender_province_id                -- 寄件省份id
          ,max(sender_province_name)             as   sender_province_name              -- 寄件省份名称
          ,max(sender_city_id)                   as   sender_city_id                    -- 寄件城市id
          ,max(sender_city_name)                 as   sender_city_name                  -- 寄件城市名称
          ,max(sender_area_id)                   as   sender_area_id                    -- 寄件区域id
          ,max(sender_area_name)                 as   sender_area_name                  -- 寄件区域名称
          ,max(sender_township)                  as   sender_township                   -- 寄件乡镇
          ,max(sender_street)                    as   sender_street                     -- 寄件街道
          ,max(sender_detailed_address)          as   sender_detailed_address           -- 寄件详细地址
          ,max(origin_id)                        as   origin_id                         -- 始发地id
          ,max(origin_name)                      as   origin_name                       -- 始发地名字
          ,max(pick_network_code)                as   pick_network_code                 -- 寄件网点code
          ,max(pick_network_name)                as   pick_network_name                 -- 寄件网点名称
          ,max(freight)                          as   freight                           -- 运费
          ,max(total_freight)                    as   total_freight                     -- 总运费
          ,max(pick_finance_code)                as   pick_finance_code                 -- 寄件财务中心code
          ,max(dispatch_finance_code)            as   dispatch_finance_code             -- 派件财务中心code
          ,max(receiver_name)                    as   receiver_name                     -- 收件人姓名
          ,max(receiver_mobile_phone)            as   receiver_mobile_phone             -- 收件人手机号
          ,max(receiver_province_id)             as   receiver_province_id              -- 收件省份id
          ,max(receiver_province_name)           as   receiver_province_name            -- 收件省份名称
          ,max(receiver_city_id)                 as   receiver_city_id                  -- 收件城市id
          ,max(receiver_city_name)               as   receiver_city_name                -- 收件城市名称
          ,max(receiver_area_id)                 as   receiver_area_id                  -- 收件区域id
          ,max(receiver_area_name)               as   receiver_area_name                -- 收件区域名称
          ,max(receiver_township)                as   receiver_township                 -- 收件乡镇
          ,max(receiver_street)                  as   receiver_street                   -- 收件街道
          ,max(receiver_detailed_address)        as   receiver_detailed_address         -- 收件详细地址
          ,max(receiver_postal_code)             as   receiver_postal_code              -- 收件邮编
          ,max(destination_id)                   as   destination_id                    -- 目的地id
          ,max(destination_name)                 as   destination_name                  -- 目的地名称
          ,max(receiver_sorting_code)            as   receiver_sorting_code             -- 收件分拣码
          ,max(waybill_input_time)               as   waybill_input_time                -- 录入时间
          ,max(input_network_code)               as   input_network_code                -- 录入网点code
          ,max(settlement_weight)                as   settlement_weight                 -- 结算重量
          ,max(customer_id)                      as   customer_id                       -- 客户id
          ,max(last_update_time)                 as   last_update_time                  -- 运单最后更新时间
          ,max(is_delete)                        as   is_delete                         -- 是否删除1未删除,2已删除
          ,max(is_plaintext)                     as   is_plaintext                      --
          ,max(real_name)                        as   real_name                         -- 实名姓名
          ,max(id_no_type)                       as   id_no_type                        -- 证件类型
          ,max(sex)                              as   sex                               -- 性别
          ,max(distribute_finance_code)          as   distribute_finance_code           -- 转运中心财务网点编码
          ,max(package_collect_weight)           as   package_collect_weight            -- 收件重量单位千克
          ,max(package_receipt_weight)           as   package_receipt_weight            -- 入仓重量单位千克
          ,max(package_arrival_weight)           as   package_arrival_weight            -- 集散到件重量单位千克
          ,max(package_inside_charge_weight)     as   package_inside_charge_weight      -- 内部计费重量单位千克
          ,max(is_distribute_scan)               as   is_distribute_scan                -- 转运中心扫描标识:1=已转运,0=未转运,默认0
          ,max(is_arrival_scan)                  as   is_arrival_scan                   -- 集散到件标识:1=是,0=否,默认0
          ,max(waybill_weight)                   as   waybill_weight                    -- 运单重量单位千克
          ,max(is_business)                      as   is_business                       -- 是否是商务件：0否,1是
          ,max(is_receive)                       as   is_receive                        -- 是否已收款1是,0否
          ,max(customer_network_code)            as   customer_network_code             -- 客户网点code
          ,max(order_id)                         as   order_id                          -- 订单编号(订单表)
          ,max(customer_order_id)                as   customer_order_id                 -- 客户订单编号(订单表)
          ,max(order_source_code)                as   order_source_code                 -- 订单来源code(订单表)
          ,max(order_source_name)                as   order_source_name                 -- 订单来源名称(订单表)
          ,max(order_type_code)                  as   order_type_code                   -- 订单类型code(订单表)
          ,max(packate_volume)                   as   packate_volume                    -- 包裹体积重单位立方厘米(订单表)
          ,max(customer_order_time)              as   customer_order_time               -- 客户下单时间(订单表)
          ,max(order_input_time)                 as   order_input_time                  -- 订单录入时间(订单表)
          ,max(order_status_code)                as   order_status_code                 -- 订单状态code(订单表)
          ,max(cancel_time)                      as   cancel_time                       -- 订单取消时间(订单表)
          ,max(real_pick_network_code)           as   real_pick_network_code            -- 实际取件网点code(订单表)
          ,max(dispatch_network_time)            as   dispatch_network_time             -- 调度网点时间(订单表)
          ,max(pick_time)                        as   pick_time                         -- 取件时间(订单表)
          ,max(assigner_name)                    as   assigner_name                     -- 委托人姓名(订单表)
          ,max(assigner_mobile_phone)            as   assigner_mobile_phone             -- 委托人手机号(订单表)
          ,max(order_update_time)                as   order_update_time                 -- 订单更新时间(订单表)
          ,max(create_by_code)                   as   create_by_code                    -- 创建人编码(订单表)
          ,max(create_by_name)                   as   create_by_name                    -- 创建人姓名(订单表)
          ,max(proxy_area_code)                  as   proxy_area_code                   -- 代理区code(订单表)
          ,max(proxy_area_name)                  as   proxy_area_name                   -- 代理区name(订单表)
          ,max(cancel_explain)                   as   cancel_explain                    -- 取消订单说明(订单表)
          ,max(customer_waybill_no)              as   customer_waybill_no               -- 客户运单号(订单表)
          ,max(sign_receipt)                     as   sign_receipt                      -- 签回单 0:否 ,1:是，2:回单(订单表)
          ,max(receipt_waybill_no)               as   receipt_waybill_no                -- 回单运单号(订单表)
          ,max(is_transfer)                      as   is_transfer                       -- 是否转寄(1否,2是)(订单表)
          ,max(order_pick_network_code)          as   order_pick_network_code           -- 寄件网点code(订单表)
          ,max(order_dispatch_network_code)      as   order_dispatch_network_code       -- 派件网点code(订单表)
          ,max(end_code)                         as   end_code                          -- 完结类型编码(tab_end_piece表)
          ,max(end_code_desc)                    as   end_code_desc                     -- 完结件编码描述(tab_end_piece表)
          ,max(end_time)                         as   end_time                          -- 完结时间(tab_end_piece表)
          ,max(end_network_code)                 as   end_network_code                  -- 完结扫描网点(tab_end_piece表)
          ,max(end_source)                       as   end_source                        -- 来源 1、问题件 2、质量工单 3、仲裁 4、客服工单(tab_end_piece表)
          ,max(order_source)                     as   order_source                      -- 订单来源(tab_statistics表)
          ,max(dt)                               as   dt
    from aggr
  group by waybill_no
    having max(dt) BETWEEN date_add('{{ execution_date | cst_ds }}', -15) AND '{{ execution_date | cst_ds }}'
      union all
    select
          waybill_no                             as   waybill_no                        -- 运单号
          ,null                                  as   waybill_status_code               -- 运单状态code
          ,null                                  as   goods_type_id                     -- 物品类型id
          ,null                                  as   goods_type_code                   -- 物品类型code
          ,null                                  as   goods_type_name                   -- 物品类型名称
          ,null                                  as   goods_name                        -- 物品名称
          ,null                                  as   package_length                    -- 包裹总长单位厘米
          ,null                                  as   package_wide                      -- 包裹总宽单位厘米
          ,null                                  as   package_high                      -- 包裹总高单位厘米
          ,null                                  as   package_volume                    -- 包裹体积重单位千克
          ,null                                  as   package_total_weight              -- 包裹总重量单位: 千克
          ,null                                  as   package_total_volume              -- 包裹总体积单位立方厘米
          ,null                                  as   package_charge_weight             -- 包裹计费重量单位千克
          ,null                                  as   box_standard_code                 -- 箱子规格code
          ,null                                  as   box_standard_name                 -- 箱子规格名称
          ,null                                  as   collect_staff_code                -- 揽件业务员code
          ,null                                  as   collect_staff_name                -- 揽件业务员名称
          ,null                                  as   collect_time                      -- 揽件时间
          ,null                                  as   dispatch_staff_code               -- 派件业务员code
          ,null                                  as   dispatch_staff_name               -- 派件业务员名称
          ,null                                  as   dispatch_time                     -- 派件时间
          ,null                                  as   dispatch_network_code             -- 派件网点code
          ,null                                  as   dispatch_network_name             -- 派件网点名称
          ,null                                  as   exception_code                    -- 异常原因编码
          ,null                                  as   exception_reason                  --
          ,null                                  as   terminal_dispatch_code            -- 三段码
          ,null                                  as   first_code                        -- 一段码
          ,null                                  as   second_code                       -- 二段码
          ,null                                  as   third_code                        -- 三段码
          ,null                                  as   is_sign                           -- 签收标识1是，0否
          ,null                                  as   sign_network_code                 -- 签收网点code
          ,null                                  as   sign_network_name                 -- 签收网点名称
          ,null                                  as   sign_finance_code                 -- 签收财务网点编码
          ,null                                  as   sign_time                         -- 签收时间
          ,null                                  as   is_abnormal                       -- 问题件标识1是，0否
          ,null                                  as   abnormal_reg_network_code         -- 问题件登记网点code
          ,null                                  as   abnormal_reg_time                 -- 问题件登记时间
          ,null                                  as   waybill_source_code               -- 运单来源code
          ,null                                  as   waybill_source_name               -- 运单来源名称
          ,null                                  as   send_code                         -- 寄件方式code
          ,null                                  as   send_name                         -- 寄件方式名称
          ,null                                  as   dispatch_code                     -- 派件方式code
          ,null                                  as   dispatch_name                     -- 派件方式名称
          ,null                                  as   is_void                           -- 是否作废件1是,0否
          ,null                                  as   is_refund                         -- 是否退件1是,0否
          ,null                                  as   is_need_receipt                   -- 是否需要需要签回单1是,0否
          ,null                                  as   receipt_no                        -- 回单编号
          ,null                                  as   customer_code                     -- 客户编号code
          ,null                                  as   customer_name                     -- 客户编号名称
          ,null                                  as   sender_name                       -- 寄件人姓名
          ,null                                  as   sender_mobile_phone               -- 寄件人手机号
          ,null                                  as   sender_province_id                -- 寄件省份id
          ,null                                  as   sender_province_name              -- 寄件省份名称
          ,null                                  as   sender_city_id                    -- 寄件城市id
          ,null                                  as   sender_city_name                  -- 寄件城市名称
          ,null                                  as   sender_area_id                    -- 寄件区域id
          ,null                                  as   sender_area_name                  -- 寄件区域名称
          ,null                                  as   sender_township                   -- 寄件乡镇
          ,null                                  as   sender_street                     -- 寄件街道
          ,null                                  as   sender_detailed_address           -- 寄件详细地址
          ,null                                  as   origin_id                         -- 始发地id
          ,null                                  as   origin_name                       -- 始发地名字
          ,null                                  as   pick_network_code                 -- 寄件网点code
          ,null                                  as   pick_network_name                 -- 寄件网点名称
          ,null                                  as   freight                           -- 运费
          ,null                                  as   total_freight                     -- 总运费
          ,null                                  as   pick_finance_code                 -- 寄件财务中心code
          ,null                                  as   dispatch_finance_code             -- 派件财务中心code
          ,null                                  as   receiver_name                     -- 收件人姓名
          ,null                                  as   receiver_mobile_phone             -- 收件人手机号
          ,null                                  as   receiver_province_id              -- 收件省份id
          ,null                                  as   receiver_province_name            -- 收件省份名称
          ,null                                  as   receiver_city_id                  -- 收件城市id
          ,null                                  as   receiver_city_name                -- 收件城市名称
          ,null                                  as   receiver_area_id                  -- 收件区域id
          ,null                                  as   receiver_area_name                -- 收件区域名称
          ,null                                  as   receiver_township                 -- 收件乡镇
          ,null                                  as   receiver_street                   -- 收件街道
          ,null                                  as   receiver_detailed_address         -- 收件详细地址
          ,null                                  as   receiver_postal_code              -- 收件邮编
          ,null                                  as   destination_id                    -- 目的地id
          ,null                                  as   destination_name                  -- 目的地名称
          ,null                                  as   receiver_sorting_code             -- 收件分拣码
          ,null                                  as   waybill_input_time                -- 录入时间
          ,null                                  as   input_network_code                -- 录入网点code
          ,null                                  as   settlement_weight                 -- 结算重量
          ,null                                  as   customer_id                       -- 客户id
          ,null                                  as   last_update_time                  -- 运单最后更新时间
          ,null                                  as   is_delete                         -- 是否删除1未删除,2已删除
          ,null                                  as   is_plaintext                      --
          ,null                                  as   real_name                         -- 实名姓名
          ,null                                  as   id_no_type                        -- 证件类型
          ,null                                  as   sex                               -- 性别
          ,null                                  as   distribute_finance_code           -- 转运中心财务网点编码
          ,null                                  as   package_collect_weight            -- 收件重量单位千克
          ,null                                  as   package_receipt_weight            -- 入仓重量单位千克
          ,null                                  as   package_arrival_weight            -- 集散到件重量单位千克
          ,null                                  as   package_inside_charge_weight      -- 内部计费重量单位千克
          ,null                                  as   is_distribute_scan                -- 转运中心扫描标识:1=已转运,0=未转运,默认0
          ,null                                  as   is_arrival_scan                   -- 集散到件标识:1=是,0=否,默认0
          ,null                                  as   waybill_weight                    -- 运单重量单位千克
          ,null                                  as   is_business                       -- 是否是商务件：0否,1是
          ,null                                  as   is_receive                        -- 是否已收款1是,0否
          ,null                                  as   customer_network_code             -- 客户网点code
          ,order_id                              as   order_id                          -- 订单编号(订单表)
          ,customer_order_id                     as   customer_order_id                 -- 客户订单编号(订单表)
          ,order_source_code                     as   order_source_code                 -- 订单来源code(订单表)
          ,order_source_name                     as   order_source_name                 -- 订单来源名称(订单表)
          ,order_type_code                       as   order_type_code                   -- 订单类型code(订单表)
          ,packate_volume                        as   packate_volume                    -- 包裹体积重单位立方厘米(订单表)
          ,customer_order_time                   as   customer_order_time               -- 客户下单时间(订单表)
          ,order_input_time                      as   order_input_time                  -- 订单录入时间(订单表)
          ,order_status_code                     as   order_status_code                 -- 订单状态code(订单表)
          ,cancel_time                           as   cancel_time                       -- 订单取消时间(订单表)
          ,real_pick_network_code                as   real_pick_network_code            -- 实际取件网点code(订单表)
          ,dispatch_network_time                 as   dispatch_network_time             -- 调度网点时间(订单表)
          ,pick_time                             as   pick_time                         -- 取件时间(订单表)
          ,assigner_name                         as   assigner_name                     -- 委托人姓名(订单表)
          ,assigner_mobile_phone                 as   assigner_mobile_phone             -- 委托人手机号(订单表)
          ,order_update_time                     as   order_update_time                 -- 订单更新时间(订单表)
          ,create_by_code                        as   create_by_code                    -- 创建人编码(订单表)
          ,create_by_name                        as   create_by_name                    -- 创建人姓名(订单表)
          ,proxy_area_code                       as   proxy_area_code                   -- 代理区code(订单表)
          ,proxy_area_name                       as   proxy_area_name                   -- 代理区name(订单表)
          ,cancel_explain                        as   cancel_explain                    -- 取消订单说明(订单表)
          ,customer_waybill_no                   as   customer_waybill_no               -- 客户运单号(订单表)
          ,sign_receipt                          as   sign_receipt                      -- 签回单 0:否 ,1:是，2:回单(订单表)
          ,receipt_waybill_no                    as   receipt_waybill_no                -- 回单运单号(订单表)
          ,is_transfer                           as   is_transfer                       -- 是否转寄(1否,2是)(订单表)
          ,order_pick_network_code               as   order_pick_network_code           -- 寄件网点code(订单表)
          ,order_dispatch_network_code           as   order_dispatch_network_code       -- 派件网点code(订单表)
          ,null                                  as   end_code                          -- 完结类型编码(tab_end_piece表)
          ,null                                  as   end_code_desc                     -- 完结件编码描述(tab_end_piece表)
          ,null                                  as   end_time                          -- 完结时间(tab_end_piece表)
          ,null                                  as   end_network_code                  -- 完结扫描网点(tab_end_piece表)
          ,null                                  as   end_source                        -- 来源 1、问题件 2、质量工单 3、仲裁 4、客服工单(tab_end_piece表)
          ,null                                  as   order_source                      -- 订单来源(tab_statistics表)
          ,dt                                    as   dt
    from ord_tmp
       where dt BETWEEN date_add('{{ execution_date | cst_ds }}', -15) AND '{{ execution_date | cst_ds }}' and waybill_no is null
)
insert overwrite table jms_dwd.dwd_waybill_order_index_dt partition(dt)
   select
        result.waybill_no                      as waybill_no                     -- 运单号
       ,result.waybill_status_code             as waybill_status_code            -- 运单状态code
       ,result.goods_type_id                   as goods_type_id                  -- 物品类型id
       ,result.goods_type_code                 as goods_type_code                -- 物品类型code
       ,result.goods_type_name                 as goods_type_name                -- 物品类型名称
       ,result.goods_name                      as goods_name                     -- 物品名称
       ,result.package_length                  as package_length                 -- 包裹总长单位厘米
       ,result.package_wide                    as package_wide                   -- 包裹总宽单位厘米
       ,result.package_high                    as package_high                   -- 包裹总高单位厘米
       ,result.package_volume                  as package_volume                 -- 包裹体积重单位千克
       ,result.package_total_weight            as package_total_weight           -- 包裹总重量单位: 千克
       ,result.package_total_volume            as package_total_volume           -- 包裹总体积单位立方厘米
       ,result.package_charge_weight           as package_charge_weight          -- 包裹计费重量单位千克
       ,result.box_standard_code               as box_standard_code              -- 箱子规格code
       ,result.box_standard_name               as box_standard_name              -- 箱子规格名称
       ,result.collect_staff_code              as collect_staff_code             -- 揽件业务员code
       ,result.collect_staff_name              as collect_staff_name             -- 揽件业务员名称
       ,result.collect_time                    as collect_time                   -- 揽件时间
       ,result.dispatch_staff_code             as dispatch_staff_code            -- 派件业务员code
       ,result.dispatch_staff_name             as dispatch_staff_name            -- 派件业务员名称
       ,result.dispatch_time                   as dispatch_time                  -- 派件时间
       ,result.dispatch_network_code           as dispatch_network_code          -- 派件网点code
       ,result.dispatch_network_name           as dispatch_network_name          -- 派件网点名称
       ,net_dispatch.franchisee_code           as dispatch_franchisee_code       -- 派件网点加盟商code
       ,net_dispatch.franchisee_name           as dispatch_franchisee_name       -- 派件网点加盟商
       ,net_dispatch.agent_code                as dispatch_agent_code            -- 派件网点代理区code
       ,net_dispatch.agent_name                as dispatch_agent_name            -- 派件网点代理区
       ,result.exception_code                  as exception_code                 -- 异常原因编码
       ,result.exception_reason                as exception_reason               --
       ,result.terminal_dispatch_code          as terminal_dispatch_code         -- 三段码
       ,result.first_code                      as first_code                     -- 一段码
       ,result.second_code                     as second_code                    -- 二段码
       ,result.third_code                      as third_code                     -- 三段码
       ,result.is_sign                         as is_sign                        -- 签收标识1是，0否
       ,result.sign_network_code               as sign_network_code              -- 签收网点code
       ,result.sign_network_name               as sign_network_name              -- 签收网点名称
       ,net_sign.franchisee_code               as sign_franchisee_code           -- 签收网点加盟商code
       ,net_sign.franchisee_name               as sign_franchisee_name           -- 签收网点加盟商
       ,net_sign.agent_code                    as sign_agent_code                -- 签收网点代理区code
       ,net_sign.agent_name                    as sign_agent_name                -- 签收网点代理区
       ,result.sign_finance_code               as sign_finance_code              -- 签收财务网点编码
       ,result.sign_time                       as sign_time                      -- 签收时间
       ,result.is_abnormal                     as is_abnormal                    -- 问题件标识1是，0否
       ,result.abnormal_reg_network_code       as abnormal_reg_network_code      -- 问题件登记网点code
       ,result.abnormal_reg_time               as abnormal_reg_time              -- 问题件登记时间
       ,result.waybill_source_code             as waybill_source_code            -- 运单来源code
       ,result.waybill_source_name             as waybill_source_name            -- 运单来源名称
       ,result.send_code                       as send_code                      -- 寄件方式code
       ,result.send_name                       as send_name                      -- 寄件方式名称
       ,result.dispatch_code                   as dispatch_code                  -- 派件方式code
       ,result.dispatch_name                   as dispatch_name                  -- 派件方式名称
       ,result.is_void                         as is_void                        -- 是否作废件1是,0否
       ,result.is_refund                       as is_refund                      -- 是否退件1是,0否
       ,result.is_need_receipt                 as is_need_receipt                -- 是否需要需要签回单1是,0否
       ,result.receipt_no                      as receipt_no                     -- 回单编号
       ,result.customer_code                   as customer_code                  -- 客户编号code
       ,result.customer_name                   as customer_name                  -- 客户编号名称
       ,result.sender_name                     as sender_name                    -- 寄件人姓名
       ,result.sender_mobile_phone             as sender_mobile_phone            -- 寄件人手机号
       ,result.sender_province_id              as sender_province_id             -- 寄件省份id
       ,result.sender_province_name            as sender_province_name           -- 寄件省份名称
       ,result.sender_city_id                  as sender_city_id                 -- 寄件城市id
       ,result.sender_city_name                as sender_city_name               -- 寄件城市名称
       ,result.sender_area_id                  as sender_area_id                 -- 寄件区域id
       ,result.sender_area_name                as sender_area_name               -- 寄件区域名称
       ,result.sender_township                 as sender_township                -- 寄件乡镇
       ,result.sender_street                   as sender_street                  -- 寄件街道
       ,result.sender_detailed_address         as sender_detailed_address        -- 寄件详细地址
       ,result.origin_id                       as origin_id                      -- 始发地id
       ,result.origin_name                     as origin_name                    -- 始发地名字
       ,result.pick_network_code               as pick_network_code              -- 寄件网点code
       ,result.pick_network_name               as pick_network_name              -- 寄件网点名称
       ,net_taking.franchisee_code             as pick_franchisee_code           -- 寄件加盟商code
       ,net_taking.franchisee_name             as pick_franchisee_name           -- 寄件加盟商
       ,net_taking.agent_code                  as pick_agent_code                -- 寄件代理区code
       ,net_taking.agent_name                  as pick_agent_name                -- 寄件代理区
       ,result.freight                         as freight                        -- 运费
       ,result.total_freight                   as total_freight                  -- 总运费
       ,result.pick_finance_code               as pick_finance_code              -- 寄件财务中心code
       ,result.dispatch_finance_code           as dispatch_finance_code          -- 派件财务中心code
       ,result.receiver_name                   as receiver_name                  -- 收件人姓名
       ,result.receiver_mobile_phone           as receiver_mobile_phone          -- 收件人手机号
       ,result.receiver_province_id            as receiver_province_id           -- 收件省份id
       ,result.receiver_province_name          as receiver_province_name         -- 收件省份名称
       ,result.receiver_city_id                as receiver_city_id               -- 收件城市id
       ,result.receiver_city_name              as receiver_city_name             -- 收件城市名称
       ,result.receiver_area_id                as receiver_area_id               -- 收件区域id
       ,result.receiver_area_name              as receiver_area_name             -- 收件区域名称
       ,result.receiver_township               as receiver_township              -- 收件乡镇
       ,result.receiver_street                 as receiver_street                -- 收件街道
       ,result.receiver_detailed_address       as receiver_detailed_address      -- 收件详细地址
       ,result.receiver_postal_code            as receiver_postal_code           -- 收件邮编
       ,result.destination_id                  as destination_id                 -- 目的地id
       ,result.destination_name                as destination_name               -- 目的地名称
       ,result.receiver_sorting_code           as receiver_sorting_code          -- 收件分拣码
       ,result.waybill_input_time              as waybill_input_time             -- 录入时间
       ,result.input_network_code              as input_network_code             -- 录入网点code
       ,result.settlement_weight               as settlement_weight              -- 结算重量
       ,result.customer_id                     as customer_id                    -- 客户id
       ,result.last_update_time                as last_update_time               -- 运单最后更新时间
       ,result.is_delete                       as is_delete                      -- 是否删除1未删除,2已删除
       ,result.is_plaintext                    as is_plaintext                   --
       ,result.real_name                       as real_name                      -- 实名姓名
       ,result.id_no_type                      as id_no_type                     -- 证件类型
       ,result.sex                             as sex                            -- 性别
       ,result.distribute_finance_code         as distribute_finance_code        -- 转运中心财务网点编码
       ,result.package_collect_weight          as package_collect_weight         -- 收件重量单位千克
       ,result.package_receipt_weight          as package_receipt_weight         -- 入仓重量单位千克
       ,result.package_arrival_weight          as package_arrival_weight         -- 集散到件重量单位千克
       ,result.package_inside_charge_weight    as package_inside_charge_weight   -- 内部计费重量单位千克
       ,result.is_distribute_scan              as is_distribute_scan             -- 转运中心扫描标识:1=已转运,0=未转运,默认0
       ,result.is_arrival_scan                 as is_arrival_scan                -- 集散到件标识:1=是,0=否,默认0
       ,result.waybill_weight                  as waybill_weight                 -- 运单重量单位千克
       ,result.is_business                     as is_business                    -- 是否是商务件：0否,1是
       ,result.is_receive                      as is_receive                     -- 是否已收款1是,0否
       ,result.customer_network_code           as customer_network_code          -- 客户网点code
       ,result.order_id                        as order_id                       -- 订单编号(订单表)
       ,result.customer_order_id               as customer_order_id              -- 客户订单编号(订单表)
       ,result.order_source_code               as order_source_code              -- 订单来源code(订单表)
       ,result.order_source_name               as order_source_name              -- 订单来源名称(订单表)
       ,result.order_type_code                 as order_type_code                -- 订单类型code(订单表)
       ,result.packate_volume                  as packate_volume                 -- 包裹体积重单位立方厘米(订单表)
       ,result.customer_order_time             as customer_order_time            -- 客户下单时间(订单表)
       ,result.order_input_time                as order_input_time               -- 订单录入时间(订单表)
       ,result.order_status_code               as order_status_code              -- 订单状态code(订单表)
       ,result.cancel_time                     as cancel_time                    -- 订单取消时间(订单表)
       ,result.real_pick_network_code          as real_pick_network_code         -- 实际取件网点code(订单表)
       ,result.dispatch_network_time           as dispatch_network_time          -- 调度网点时间(订单表)
       ,result.pick_time                       as pick_time                      -- 取件时间(订单表)
       ,result.assigner_name                   as assigner_name                  -- 委托人姓名(订单表)
       ,result.assigner_mobile_phone           as assigner_mobile_phone          -- 委托人手机号(订单表)
       ,result.order_update_time               as order_update_time              -- 订单更新时间(订单表)
       ,result.create_by_code                  as create_by_code                 -- 创建人编码(订单表)
       ,result.create_by_name                  as create_by_name                 -- 创建人姓名(订单表)
       ,result.proxy_area_code                 as proxy_area_code                -- 代理区code(订单表)
       ,result.proxy_area_name                 as proxy_area_name                -- 代理区name(订单表)
       ,result.cancel_explain                  as cancel_explain                 -- 取消订单说明(订单表)
       ,result.customer_waybill_no             as customer_waybill_no            -- 客户运单号(订单表)
       ,result.sign_receipt                    as sign_receipt                   -- 签回单 0:否 ,1:是，2:回单(订单表)
       ,result.receipt_waybill_no              as receipt_waybill_no             -- 回单运单号(订单表)
       ,result.is_transfer                     as is_transfer                    -- 是否转寄(1否,2是)(订单表)
       ,result.order_pick_network_code         as order_pick_network_code        -- 寄件网点code(订单表)
       ,result.order_dispatch_network_code     as order_dispatch_network_code    -- 派件网点code(订单表)
       ,result.end_code                        as end_code                       -- 完结类型编码(tab_end_piece表)
       ,result.end_code_desc                   as end_code_desc                  -- 完结件编码描述(tab_end_piece表)
       ,result.end_time                        as end_time                       -- 完结时间(tab_end_piece表)
       ,result.end_network_code                as end_network_code               -- 完结扫描网点(tab_end_piece表)
       ,result.end_source                      as end_source                     -- 来源 1、问题件 2、质量工单 3、仲裁 4、客服工单(tab_end_piece表)
       ,result.order_source                    as statistics_order_source        -- 订单来源(tab_statistics表)
       ,dt
    from result
    left join network net_taking    on coalesce(result.pick_network_code,result.order_id)      = net_taking.code
    left join network net_dispatch  on coalesce(result.dispatch_network_code,result.order_id)  = net_dispatch.code
    left join network net_sign      on coalesce(result.sign_network_code,result.order_id)      = net_sign.code
  distribute by dt,pmod(hash(waybill_no),36) ;